package com.southwind.dao.impl;

import com.southwind.dao.MoveoutDao;
import com.southwind.entity.Moveout;
import com.southwind.entity.Student;
import com.southwind.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MoveoutDaoImpl implements MoveoutDao {


    @Override
    public Integer save(Moveout moveout) {
        Connection con = JDBCUtil.getConnection();
        PreparedStatement stmt = null;
        Integer result = null;
        String sql = "insert into moveout(student_id, dormitory_id, reason, create_date) value(?,?,?,?)";
        try {
            stmt = con.prepareStatement(sql);
            stmt.setInt(1, moveout.getStudentId());
            stmt.setInt(2, moveout.getDormitoryId());
            stmt.setString(3, moveout.getReason());
            stmt.setString(4, moveout.getCreateDate());
            result = stmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.release(con,stmt, null);
        }

        return result;
    }

    @Override
    public List<Moveout> list() {
        Connection con = JDBCUtil.getConnection();
        PreparedStatement stmt = null;
        ResultSet resultSet = null;
        String sql = "select m.id,s.name,d.name,m.reason,m.create_date from moveout m,student s,dormitory d where m.student_id = s.id and m.dormitory_id = d.id";
        List<Moveout> list = new ArrayList<>();
        try {
            stmt = con.prepareStatement(sql);
            resultSet = stmt.executeQuery();
            while(resultSet.next())
            {
                Integer id = resultSet.getInt(1);
                String studentName = resultSet.getString(2);
                String dormitoryName = resultSet.getString(3);
                String reason = resultSet.getString(4);
                String create_date = resultSet.getString(5);
                list.add(new Moveout(id, studentName, dormitoryName, reason, create_date));
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.release(con,stmt,resultSet);
        }

        return list;
    }

    @Override
    public List<Moveout> search(String key, String value) {
        Connection con = JDBCUtil.getConnection();
        PreparedStatement stmt = null;
        ResultSet resultSet = null;
        String sql = "select m.id,s.name,d.name,m.reason,m.create_date from moveout m,student s,dormitory d where m.student_id = s.id and m.dormitory_id = d.id";
        String keySql = null;
        if(key.equals("studentName"))
        {
            keySql = " and s.name";
        }
        if(key.equals("dormitoryName"))
        {
            keySql = " and d.name";
        }
        sql = sql + keySql + " like '%" + value + "%'";
        List<Moveout> list = new ArrayList<>();
        try {
            stmt = con.prepareStatement(sql);
            resultSet = stmt.executeQuery();
            while(resultSet.next())
            {
                Integer id = resultSet.getInt(1);
                String studentName = resultSet.getString(2);
                String dormitoryName = resultSet.getString(3);
                String reason = resultSet.getString(4);
                String create_date = resultSet.getString(5);
                list.add(new Moveout(id, studentName, dormitoryName, reason, create_date));
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.release(con,stmt,resultSet);
        }

        return list;
    }
}
